/*
 * @Author: your name
 * @Date: 2022-04-11 14:41:02
 * @LastEditTime: 2022-05-03 08:55:30
 * @LastEditors: your name
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \VScodeToWeb\bookWebApi\sqlite\initdb.js
 */
const common = require('../util/common')
const fs = require('fs')
const sqlite3 = require('sqlite3').verbose()

const config = require('../config')

const bookCreateSql = `
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        title VARCHAR(255) NOT NULL,
        pic TEXT,
        author VARCHAR(255),
        publisher VARCHAR(255),
        translator VARCHAR(255),
        pubdate VARCHAR(255),
        pages VARCHAR(255),
        price VARCHAR(255),
        binding VARCHAR(255),
        series VARCHAR(255),
        isbn VARCHAR(255) NOT NULL,
        created_time DATETIME NOT NULL,
        updated_time DATETIME NOT NULL
    );
`
// email地址和手机号、证件号，
// 微信和支付宝 认证关联 openId
// role:1.管理员用户，2.普通用户
const userCreateSql = `
    CREATE TABLE IF NOT EXISTS users(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        username VARCHAR(20) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        nickname VARCHAR(255),
        truename VARCHAR(255),
        avatar VARCHAR(255),
        role INTEGER NOT NULL,
        last_login_time DATETIME NOT NULL,
        last_login_ip VARCHAR(255) NOT NULL,
        login_count INTEGER DEFAULT 0,
        created_time DATETIME NOT NULL,
        created_ip VARCHAR(255) NOT NULL,
        updated_time DATETIME NOT NULL
    );
`

const adminUserSql = `
    INSERT INTO users (
        username, password, nickname, truename, avatar, role,
        last_login_time, last_login_ip, login_count,
        created_time, created_ip, updated_time
    ) VALUES (
        'admin', '${common.md5pwd('123456')}', '系统管理员', '管理员1',
        './images/default.png', 1, 0, 'never login', 0, ${Date.now()}, 'system init',
        ${Date.now()}
    );
`

function SqliteDb() {
  this.dbFile = config.dbFile

  if (!fs.existsSync(this.dbFile)) {
    common.log('创建Sqlite数据库文件')
    this.InitDB()
  }
} // 初始化sqlite3数据库
SqliteDb.prototype.InitDB = function () {
  let db = new sqlite3.Database(this.dbFile, err => {
    if (err) {
      common.log(`输出数据库错误信息：${err}`)
    }
  })
  db.serialize(() => {
    // 创建书表
    db.run(bookCreateSql)
    console.log('books数据表创建成功')
    db.run(userCreateSql)
    console.log('users数据表创建成功')
    db.run(adminUserSql)
    console.log('新建管理员成功')
  })
  db.close(err => {
    if (err) {
      common.log(`输出数据库错误信息：${err}`)
    }
  })
}

module.exports = SqliteDb
